Parts 1,2 & 3 focusssed more on the most useful improvements and enhancements of Database administration: Performance Tuning, RMAN, Data Guard, ASM and Clusterware. This part of the series will mainly focus on some of the new features that are useful to developers.
Read Oracle Database 12c New Features – Part 1
Read Oracle Database 12c New Features – Part 2
Read Oracle Database 12c New Features – Part 3
Part 4 covers:
- How to truncate a master table while child tables contain data
- Limiting ROWS for Top-N query results
- Miscellaneous SQL*Plus enhancements
- Session level sequences
- WITH clause improvements
- Extended data types
Truncate table CASCADE
In the previous releases, there wasn’t a direct option provided to truncate a master table while it is referred to by the child tables and child records exist. The TRUNCATE TABLE with CASCADE option in 12c truncates the records in the master table and automatically initiates recursive truncate on child tables too, subject to foreign key reference as DELETE ON CASCADE. There is no CAP on the number of recursive levels as it will apply on all child, grand child and great grandchild etc.
This enhancement gets rid of the prerequisite to truncate all child records before truncating a master table. The new CASCADE clause can also be applied on table partitions and sub-partitions etc.
|
1 2 3 |
SQL> TRUNCATE TABLE <table_name> CASCADE; SQL> TRUNCATE TABLE <table_name> PARTITION <partition_name> CASCADE; |
An ORA-14705 error will be thrown if no ON DELETE CASCADE option is defined with the foreign keys of the child tables.
ROW limiting for Top-N result queries
There are various indirect approaches/methods exist to fetch Top-N query results for top/bottom rows in the previous releases. In 12c, retrieving Top-N query results for top/bottom rows simplified and become straight forward with the new FETCH FIRST|NEXT|PERCENT clauses.
In order to retrieve top 10 salaries from EMP table, use the following new SQL statement:
|
1 2 |
SQL> SELECT eno,ename,sal FROM emp ORDER BY SAL DESC FETCH FIRST 10 ROWS ONLY; |
The following example fetches all similar records of Nth row. For example, if the 10th row has salary of 5000 value, and there are other employees whose salary matches with the Nth value, the will also be fetched upon mentioning WITH TIES clause.
|
1 2 |
SQL> SELECT eno,ename,sal FROM emp ORDER BY SAL DESC FETCH FIRST 10 ROWS ONLY WITH TIES; |
The following example limits the fetch to 10 per cent from the top salaries in the EMP table:
|
1 2 |
SQL> SELECT eno,ename,sal FROM emp ORDER BY SAL DESC FETCH FIRST 10 PERCENT ROWS ONLY; |
The following example offsets the first 5 rows and will display the next 5 rows from the table:
|
1 2 |
SQL> SELECT eno,ename,sal FROM emp ORDER BY SAL DESC OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY; |
All these limits can be very well used within the PL/SQL block too.
|
1 2 3 4 |
BEGIN SELECT sal BULK COLLECT INTO sal_v FROM EMP FETCH FIRST 100 ROWS ONLY; END; |
Miscellaneous SQL*Plus enhancements
Implicit Results on SQL*Plus: SQL*Plus in 12c returns results from an implicit cursor of a PL/SQL block without actually binding it to a RefCursor. The new dbms_sql.return_result procedure will return and formats the results of SELECT statement query specified within PL/SQL block. The following code descries the usage:
|
1 2 3 4 5 6 7 8 9 |
SQL> CREATE PROCEDURE mp1 as res1 sys_refcursor; BEGIN open res1 for SELECT eno,ename,sal FROM emp; dbms_sql.return_result(res1); END; SQL> execute mp1; |
When the procedure is executed, it return the formatted rows on the SQL*Plus.
Display invisible columns: In Part 1 of this series, I have explained and demonstrated about invisible columns new feature. When the columns are defined as invisible, they won’t be displayed when you describe the table structure. However, you can display the information about the invisible columns by setting the following on the SQL*Plus prompt:
|
1 |
SQL> SET COLINVISIBLE ON|OFF |
The above setting is only valid for DESCRIBE command. It has not effect on the SELECT statement results on the invisible columns.
Session level sequences
A new SESSION level database sequence can be created now in 12c to support the session level sequence values. These types of sequences are most useful and suitable on global temporary tables that have session level existence.
Session level sequences produce a unique range of values that are limited within the session, not across the sessions. Once the session ends, the state of the session sequences also goes away. The following example explains creating a session level sequence:
|
1 2 3 |
SQL> CREATE SEQUENCE my_seq START WITH 1 INCREMENT BY 1 SESSION; SQL> ALTER SEQUENCE my_seq GLOBAL|SESSION; |
The CACHE, NOCACHE, ORDER or NOORDER clauses are ignored for SESSION level sequences.
WITH clause improvements
In 12c, you can have faster running PL/SQL function/procedure in SQL, that are defined and declared within the WITH clause of SQL statements. The following examples demonstrate how to define and declare a procedure or function within the WITH clause:
|
1 2 3 4 5 6 7 |
WITH PROCEDURE|FUNCTION test1 (…) BEGIN <logic> END; SELECT <referece_your_function|procedure_here> FROM table_name; / |
Although you can’t use the WITH clause directly in the PL/SQL unit, it can be referred through a dynamic SQL within that PL/SQL unit.
Extended data types
In 12c, the data type VARCHAR2, NAVARCHAR2, and RAW size will support up to 32,767 bytes in contrast to 4,000 and 2,000 in the earlier releases. The extended character size will reduce the use of going for LOB data types, whenever possible. In order to enable the extended character size, you will have to set the MAX_STRING_SIZE initialization database parameter to EXTENDED.
The following procedure need to run to use the extended data types:
- Shutdown the database
- Restart the database in UPGRADE mode
- Modify the parameter: ALTER SYSTEM SET MAX_STRING_SIZE=EXTENDED;
- Execute utl32k.sql as sysdba : SQL> @?/rdbms/admin/utl32k.sql
- Shutdown the database
- Restart the database in READ WRITE mode
In contrast to LOB data types, the extended data types columns in ASSM tablespace management are stored as SecureFiles LOBs, and in non-ASSM tablespace management they stored as BasciFiles LOBs.
Note: Once modified, you can’t change the settings back to STANDARD.
Load comments